SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_GetUsersByRolename]
	@PortalID	int,
	@Rolename	nvarchar(50)
AS
	SELECT     
		U.*, 
		UP.PortalId, 
		UP.Authorised, 
		UP.IsDeleted,
		UP.RefreshRoles 
	FROM dbo.DnnProduct_UserPortals AS UP 
			RIGHT OUTER JOIN dbo.DnnProduct_UserRoles  UR 
			INNER JOIN dbo.DnnProduct_Roles R ON UR.RoleID = R.RoleID 
			RIGHT OUTER JOIN dbo.DnnProduct_Users AS U ON UR.UserID = U.UserID 
		ON UP.UserId = U.UserID	
	WHERE ( UP.PortalId = @PortalID OR @PortalID IS Null )
		AND (UP.IsDeleted = 0 OR UP.IsDeleted Is NULL)
		AND (R.RoleName = @Rolename)
		AND (R.PortalId = @PortalID OR @PortalID IS Null )
	ORDER BY U.FirstName + ' ' + U.LastName

GO
